<?php
/**
 * sincronizza il db con il metabase
 */
 
class syncro{	
	private $aDBPar = null;		//parametri di connessione al database
	private $aMBPar = null;		//parametri di connessione al metabase
	private $objDB = null; 			// connessione al database
	private $objMB = null;			//connessione al metabase
	private $aObjMB = array(); 		//array che contiene tutti gli oggetti del MB 
	private $aObjDB = array(); 		//array che contiene tutti gli oggetti del DB 
	private $aSQLSync = array(); 	//array che contiene gli statement SQL da eseguire per la sincronizzreqtype
	public $error = "";
	public $errno = 0;
	public $aEntAltered = array();
	public $aEntCreated = array();
	public $aEntDeleted = array();
	
	public function __construct($aDBPar, $aMBPar, $aOpzioni = null) {
		$this->reqtype = 'vis';
		if (is_array($aOpzioni)) {
			if (isset($aOpzioni['reqtype'])) {$this->reqtype = $aOpzioni['reqtype'];}
		}
		$this->aDBPar = $aDBPar;
		$this->aMBPar = $aMBPar;
		$this->objDB = new SDAO($aDBPar);
		$this->errno = $this->objDB->errno;
		$this->error = $this->objDB->error;
		
		if($this->errno != 0){
			$nomeDB = $this->aDBPar["dbName"];
			$this->error .= "<br>"._t("Il database")." $nomeDB "._t("non esiste. E' necessario crearlo manualmente.");
			return;
		}
		$this->objMB = new SDAO($aMBPar);
		$this->errno = $this->objMB->errno;
		$this->error = $this->objMB->error;
		//se tutto @ andato a buon fine: carico i dati dal MB, dal DB, 
		//li confronto ed eseguo la sync
		if($this->errno != 0) return;
		$this->loadMBData();
		if($this->errno != 0) return;
		$this->loadDBSchema();
		if($this->errno != 0) return;
		$this->compare();
		if($this->errno != 0) return;
		if($this->reqtype == 'agg') $this->sync();
	}
	
	public function __destruct() {
      $this->objDB = null; 
      $this->objMB = null;
   }

   private function backupDB(){
   	//crea una copia di salvataggio del db
   }
   
   private function loadMBData(){
   	//carico tutte le entity presenti nel MB
   	$sSQL = "SELECT * FROM entity ORDER BY entity";
   	$this->objMB->query($sSQL, "ent");
   	while($row = $this->objMB->fetch("ent")){
			//carico tutti gli attributi dell'entity
			$sSQL = "SELECT * FROM attribute a 
					LEFT OUTER JOIN domain d ON a.domain=d.domain 
					WHERE entity='".str_replace("'", "''", $row["entity"])."' ORDER BY sequence";
			$this->objMB->query($sSQL, "attr");
			while($rowAttr = $this->objMB->fetch("attr")){
				$sSQL = "SELECT count(*) as isChildAttr FROM relationship_key " .
						"WHERE child_entity='".str_replace("'", "''", $row["entity"])."' " .
						"AND child_attribute='".$rowAttr["attribute"]."'";
				$this->objMB->query($sSQL, "ch_attr");
				if($rowChAttr = $this->objMB->fetch("ch_attr")){
					$rowAttr["isChildAttr"] = $rowChAttr["isChildAttr"];
				}
				$this->aObjMB[$row["entity"]]["attr"][$rowAttr["attribute"]] = $rowAttr;
			}
			$this->aObjMB[$row["entity"]]["ent"] = $row;
   	}   	
   }
   
   private function compare(){
   	/**
   	tipi di campo
   	S = string				V = string variable
		D = date					T = date-time
		N = number				B = boolean (0,1)
		I = integer				X = text
		L = long-text
		*/
	foreach($this->aObjMB as $entMB=>$aAttr){
   		/**dbg_(print_r($this->aObjDB ,1));*/
   		/**dbg_(print_r($this->aObjMB ,1));*/
   		if(isset($this->aObjDB[strtoupper($entMB)])){
			//l'entity esiste gi@
   			$primKeyFields = "";
	   		$sepPK = "";
	   		$havePK = false;
	   		$oldPrimKeyFields = "";
	   		$sepOldPK = "";
	   		foreach($aAttr as $attrMb=>$aDefAttr){
   				if(isset($this->aObjDB[strtoupper($entMB)][strtoupper($attrMb)])){
   					//l'attribute esiste: va modificato
   					if(isset($this->aObjDB[strtoupper($entMB)][strtoupper($attrMb)]["pk"]))
   						$havePK = true;
   					$sSQLAlter = $this->alterAttr($entMB, $attrMb);
				   	if($sSQLAlter != "" && $this->errno == 0){
				   		$this->aSQLSync[] = array($sSQLAlter, _t("Attributo: ").$attrMb." "._t("dell'entity").": $entMB "._t("modificato"));
				   		$this->aEntAltered[$entMB]['a'][$attrMb]['old'] = $this->getDefDB($this->aObjDB[$entMB][$attrMb]);
				   		$this->aEntAltered[$entMB]['a'][$attrMb]['new'] = $this->getDefMB($aDefAttr);
   					}   	
					$sSQLInd = $this->alterIndex($entMB, $attrMb);
				   	if($sSQLInd != "" && $this->errno == 0){
				   		$this->aSQLSync[] = array($sSQLInd, _t("Attributo: ").$attrMb." "._t("dell'entity").": $entMB "._t("indice modificato"));
				   		if ($sSQLInd[0]=='C')$this->aEntAltered[$entMB]['i'][$attrMb]['new'] = $attrMb;
				   		if ($sSQLInd[0]!='C')$this->aEntAltered[$entMB]['i'][$attrMb]['old'] = $attrMb;
   					}
   					if($aDefAttr["primary_key"] == "Y"){
   						$primKeyFields .= $sepPK.$attrMb;
   						$sepPK = ", ";
   					} 
   					if(isset($this->aObjDB[strtoupper($entMB)][strtoupper($attrMb)]["pk"])){
   						$oldPrimKeyFields .= $sepOldPK.$attrMb;
   						$sepOldPK = ", ";
   					}   	
   				}else{
   					//l'attribute non esiste: va creato
   					if(isset($this->aObjDB[strtoupper($entMB)][strtoupper($attrMb)]["pk"]))
   						$havePK = true;
			   		$this->aEntAltered[$entMB]['a'][$attrMb]['new'] = $this->getDefMB($aDefAttr);
   					$sSQLAlter = "ALTER TABLE $entMB ADD COLUMN $attrMb ";
   					$sSQLAlter .= $this->getSQLAttr($aDefAttr);
	   				$sSQLAlter .= "\n";
	   				$this->aSQLSync[] = array($sSQLAlter, _t("Attributo")." $attrMb "._t("dell'entity").":$entMB "._t("creato"));
   					if($aDefAttr["indexed"] == "Y" && $aDefAttr["primary_key"] == "N"){
	   					$sSQLIndex = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
	   					$this->aSQLSync[] = array($sSQLIndex, _t("Indice creato"));
	   					$this->aEntAltered[$entMB]['i'][$attrMb]['new'] = $attrMb;
	   				}
	   				if($aDefAttr["indexed"] == "N" && $aDefAttr["primary_key"] == "N" && $aDefAttr["isChildAttr"]){
	   					$sSQLIndex = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
	   					$this->aSQLSync[] = array($sSQLIndex, _t("Indice creato"));
	   					$this->aEntAltered[$entMB]['i'][$attrMb]['new'] = $attrMb;
	   				}
   					if($aDefAttr["primary_key"] == "Y"){
   						$primKeyFields .= $sepPK.$attrMb;
   						$sepPK = ", ";
   					} 	
   					if(isset($this->aObjDB[strtoupper($entMB)][strtoupper($attrMb)]["pk"])){
   						$oldPrimKeyFields .= $sepOldPK.$attrMb;
   						$sepOldPK = ", ";
   					}   	   	
   				}
   			}
   			if($primKeyFields != "" && $this->errno == 0 && $primKeyFields != $oldPrimKeyFields){
		   		if($havePK){
			   		//elimino la chiave preesistente (se esiste)
			   		$sSQL = "ALTER TABLE $entMB DROP PRIMARY KEY";
			   		$this->aSQLSync[] = array($sSQL, _t("Entity").": $entMB "._t("chiave primaria")." [$primKeyFields] "._t("eliminata"));
		   		}
		   		$sSQL = "ALTER TABLE $entMB ADD CONSTRAINT ".substr("pk_$entMB", 0, 18)." PRIMARY KEY ($primKeyFields) ";
		   		$this->aSQLSync[] = array($sSQL, _t("Entity").": $entMB "._t("chiave primaria")." [$primKeyFields] "._t("creata"));
	   			$this->aEntAltered[$entMB]['i']['PRIMARY KEY']['old'] = $oldPrimKeyFields;
	   			$this->aEntAltered[$entMB]['i']['PRIMARY KEY']['new'] = $primKeyFields;
				}
   			foreach($this->aObjDB[$entMB] as $attrDB => $aFeature) {
   				if (!isset($aAttr['$attrDB'])) {
	   				$this->aEntAltered[$entMB]['a'][$attrDB]['old']=$this->getDefDB($aFeature);
   				}
	   		}
   		} else {
   			//l'entity non esiste: va creata
   			if(count($aAttr) > 0){
	   			$sSQLCreate = "CREATE TABLE $entMB (\n";
	   			$primKeyFields = "";
	   			$sepPK = "";
	   			$sepAttr = "";
	   			$aSQLIndex = array();
	   			foreach($aAttr as $attrMb=>$aDefAttr){
	   				$this->aEntCreated[$entMB]['a'][$attrMb] = $this->getDefMB($aDefAttr);
	   				$sSQLCreate .= $sepAttr.$attrMb;
	   				$sSQLCreate .= $this->getSQLAttr($aDefAttr);
	   				$sSQLCreate .= "\n";  
	   				if($aDefAttr["primary_key"] == "Y"){
	   					$primKeyFields .= $sepPK.$attrMb;
	   					$sepPK = ", ";
	   				} 
	   				if($aDefAttr["indexed"] == "Y" && $aDefAttr["primary_key"] == "N"){
	   					$aSQLIndex[] = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
	   					$this->aEntCreated[$entMB]['i'][$attrMb] = $attrMb;
	   				}	
					if($aDefAttr["indexed"] == "N" && $aDefAttr["primary_key"] == "N" && $aDefAttr["isChildAttr"]){
	   					$aSQLIndex["$entMB ($attrMb)"] = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
	   					$this->aEntCreated[$entMB]['i'][$attrMb] = $attrMb;
	   				}
	   				$sepAttr = ", ";			
	   			} 
	   			if($primKeyFields != "") {
	   				$sSQLCreate .= $sepAttr." PRIMARY KEY ($primKeyFields)"; 			
	   				$this->aEntCreated[$entMB]['i']['PRIMARY KEY'] = $primKeyFields;
	   			}
	   			$sSQLCreate .= ")\n";
	   			$this->aSQLSync[] = array($sSQLCreate, _t("Entity ").$entMB.": "._t("creata"));
	   			foreach($aSQLIndex as $sSQL){
	   				$this->aSQLSync[] = array($sSQL, _t("Indice creato"));
	   			}
	   		}
   		}
   	}
   	foreach($this->aObjDB as $entDB=>$aFields){
   		if (!isset($this->aObjMB[$entDB])) {
   			foreach($aFields as $attrDB => $aFeature)
	   			$this->aEntDeleted[$entDB][$attrDB]=$this->getDefDB($aFeature);
   		}
   	}
   }
   
   private function getDefDB($aFeature) {
   	$desType = $aFeature['Type'];
   	if ($aFeature['Extra']!='') $desType .= ' '.$aFeature['Extra'];
   	if ($aFeature['Default']!='') $desType .= ' def: '.$aFeature['Default'];
   	return $desType;
   }
   
   private function getDefMB($aDefAttr) {
   	$attrType = strtolower($aDefAttr["type"]);
   	$length = ($aDefAttr["length"]==0)?$aDefAttr["d_length"]:$aDefAttr["length"];
   	$decimals = ($aDefAttr["decimals"]==0)?$aDefAttr["d_decimals"]:$aDefAttr["decimals"];  	
   	switch($attrType){
			case "string": //S = string
				$desType = "char(".$length.") def: '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "variable-string": //V = string variable
				$desType = "varchar(".$length.") def: '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "integer": // I = integer
				if ($aDefAttr["primary_key"] == 'X') {
					$desType = "int(11) AUTO_INCREMENT";
				} else {
					$desType = "int(11) def: ".intval($aDefAttr["default_value"]);
				}
				break;
			case "date": // D = date
					$desType = "date";
				if ($aDefAttr["default_value"] != '')
					$desType  .= " def: '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "date-time": // T = date-time
				$desType = "datetime  def: '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "number": // N = number
				$desType = "decimal(".$length.",".$decimals.") def: ".
				number_format($aDefAttr["default_value"],$decimals);
				break;
			case "boolean": // B = boolean (0,1)
				$desType = "char(1)  def: '".(($aDefAttr["default_value"]=='1')?'1':'0')."'";
				break;
			case "text": // T = text
				$desType = "text def: ''";
				break;
			case "long-text": // L = long-text
				$desType = "text def: ''";
				break;
			case "blob": // O = blob
				$desType = "blob";
				break;
			default:
				$desType = "varchar(80)  def: ''";
		}
		return $desType;
   }
   
   private function getSQLAttr($aDefAttr){
   	$sSQLAttr = "";
   	$attrType = strtolower($aDefAttr["type"]);
   	switch($attrType){
			case "string": //S = string
				$sSQLAttr .= " char(".$aDefAttr["length"].") NOT NULL DEFAULT ''";
				break;
			case "variable-string": //V = string variable
				$sSQLAttr .= " varchar(".$aDefAttr["length"].")  NOT NULL DEFAULT ''";
				break;
			case "integer": // I = integer
				$sSQLAttr .= " integer  NOT NULL DEFAULT 0";
				break;
			case "date": // D = date
				$sSQLAttr .= " date ";
				break;
			case "date-time": // T = date-time
				$sSQLAttr .= " timestamp ";
				break;
			case "number": // N = number
				$sSQLAttr .= " decimal(".$aDefAttr["length"].",".$aDefAttr["decimals"].")  NOT NULL DEFAULT 0";
				break;
			case "boolean": // B = boolean (0,1)
				$sSQLAttr .= " char(1)  NOT NULL DEFAULT '0'";
				break;
			case "text": // T = text
				$sSQLAttr .= " clob(".$aDefAttr["length"].") NOT NULL DEFAULT ''";
				break;
			case "long-text": // L = long-text
				$sSQLAttr .= " clob(".$aDefAttr["length"].") NOT NULL DEFAULT ''";
				break;
			case "blob": // O = blob
				$sSQLAttr .= " blob(".$aDefAttr["length"].") NOT NULL DEFAULT ''";
				break;
			default:
				$sSQLAttr .= " varchar(80)  NOT NULL DEFAULT ''";
		}
		return $sSQLAttr;
   }
   
   private function sync(){
   	//eseguo la synchro
   	foreach($this->aSQLSync as $elem=>$aStmt){
   		if(!$this->objDB->query($aStmt[0], "sync", array("contOnErr"=>true))){
   			$this->errno = $this->objDB->errno;
   			$this->error .= "<br>".$this->objDB->error."<br>".$aStmt[0];	
   		}
   	}
   }
   
   private function loadDBSchema(){
   	/**
   	 * Carica la struttura di un db DB2
   	 */
   	//carico le tabelle
   	$sSQL = "SELECT * FROM SYSIBM.SYSTABLES WHERE creator='".strtoupper($this->aDBPar["schema"])."'";
   	if($this->objDB->query($sSQL, "ent")){
 			while($rowEnt = $this->objDB->fetch("ent")){
		   	$entita = $rowEnt["name"];
		   	//carico i campi della tabella
		   	$sSQL = "SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME='".strtoupper($entita)."' AND TBCREATOR='".strtoupper($this->aDBPar["schema"])."';";
		 		if($this->objDB->query($sSQL, "attr")){
		 			while($rowAttr = $this->objDB->fetch("attr")){
		 				//individuo gli indici collegati all'attribute
		 				$sSQLIND = "SELECT * FROM SYSIBM.SYSINDEXES WHERE TBCREATOR='".strtoupper($this->aDBPar["schema"])."' AND TBNAME='".strtoupper($entita)."' AND COLNAMES LIKE '%+".strtoupper($rowAttr["name"])."%'";
		 				$this->objDB->query($sSQLIND, "index");
		 				$rowIndex = $this->objDB->fetch("index");
		 				if($rowIndex)
		 					$rowAttr["indexes"] = $rowIndex;
		 				//individuo le PK collegate all'attribute'
		 				$sSQLPK = "SELECT * FROM SYSIBM.SYSKEYCOLUSE WHERE TBCREATOR='".strtoupper($this->aDBPar["schema"])."' AND TBNAME='".strtoupper($entita)."' AND COLNAME='".strtoupper($rowAttr["name"])."'";
		 				$this->objDB->query($sSQLPK, "pk");
		 				$rowPK = $this->objDB->fetch("pk");
		 				if($rowPK)
		 					$rowAttr["pk"] = $rowPK;
		 				$this->aObjDB[$entita][$rowAttr["name"]] = $rowAttr;	 				
		 			} 			
		 		}else{
		 			$this->errno = $this->objMB->errno;
		   		$this->error = $this->objMB->error;
		   		return false;
		 		}
		 	} 			
 		}else{
 			$this->errno = $this->objMB->errno;
   		$this->error = $this->objMB->error;
   		return false;
 		}
   }
   
   private function alterAttr($entMB, $attrMb){
   	//costruisce lo statement di alter table se il campo @ stato variato.
   	$sSQL = "";
   	$aAttrMB = $this->aObjMB[$entMB]["attr"][$attrMb];
   	$aAttrDB = $this->aObjDB[strtoupper($entMB)][strtoupper($attrMb)];
   	//echo("<br>MB: ".$aAttrMB["type"]."(".$aAttrMB["length"].")"." DB: ".$aAttrDB["coltype"]." (".$aAttrDB["length"].").");
   	if($aAttrMB["type"] == "V" && trim($aAttrDB["coltype"]) == "VARCHAR" && (int)$aAttrMB["length"] > (int)$aAttrDB["length"]){
   		//echo("<b>VARIATO</b>");
   		$sSQL = "ALTER TABLE $entMB ALTER COLUMN $attrMb SET DATA TYPE varchar(".$aAttrMB["length"].")";
   	} 
   	return $sSQL;
   }
   
   private function alterIndex($entMB, $attrMb){
   	//costruisce lo statement di crereqtype dell'indice se il campo non lo @ ancora.
   	//o lo statement di eliminreqtype dell'indice se il campo non lo @ pi@ 
   	//NB: gli attributi che sono child_attribute di una relationship sono indi-
   	//		cizzati di default, anche se non lo sono esplicitamente.
   	$sSQL = "";
   	$aAttrMB = $this->aObjMB[$entMB]["attr"][$attrMb];
   	$aAttrDB = $this->aObjDB[strtoupper($entMB)][strtoupper($attrMb)];
   	if($aAttrMB["indexed"] == "Y" && !isset($aAttrDB["indexes"])){
   		$sSQL = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
   	}else if($aAttrMB["indexed"] == "N" && isset($aAttrDB["indexes"])&& !$aAttrMB["isChildAttr"]){
   		$sSQL = "ALTER TABLE $entMB DROP INDEX $attrMb";
   	}else if($aAttrMB["primary_key"] == "N" && $aAttrMB["indexed"] == "N" && !isset($aAttrDB["indexes"]) && $aAttrMB["isChildAttr"]){
   		$sSQL = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
   	}
   	
   	return $sSQL;   	
   }
}
?>
